package com.nova.edc.service.system.impl;

import com.nova.edc.dao.system.AreaPointDao;
import com.nova.edc.dao.system.CustomerInfoDao;
import com.nova.edc.dao.system.DeviceLightDao;
import com.nova.edc.dao.system.DeviceTypeDao;
import com.nova.edc.model.energy.Gather;
import com.nova.edc.model.system.*;
import com.nova.edc.model.util.ExcelArea;
import com.nova.edc.service.collect.StatusService;
import com.nova.edc.service.energy.GatherService;
import com.nova.edc.service.system.AreaPointService;
import com.nova.edc.service.system.CustomerAreaService;
import com.nova.edc.service.system.DeviceAirService;
import com.nova.frame.dao.buzz.query.Order;
import com.nova.frame.dao.buzz.query.Query;
import com.nova.frame.dao.criterion.Restrictions;
import com.nova.frame.dao.pager.Page;
import com.nova.frame.dao.pager.PageList;
import com.nova.frame.dao.parsing.SQLParser;
import com.nova.frame.utils.JdbcUtils;
import com.nova.frame.utils.StringUtils;
import com.nova.module.filter.query.Filter;
import com.nova.module.sql.SqlBuffer;
import com.seio.edc.common.Constants;
import org.springframework.beans.factory.annotation.Autowired;

import java.sql.Timestamp;
import java.util.*;

public class AreaPointServiceImpl implements AreaPointService {

    @Autowired
    private AreaPointDao areaPointDao;
    @Autowired
    private DeviceTypeDao deviceTypeDao;
    @Autowired
    private CustomerInfoDao novaCustomerInfoDao;
    @Autowired
    private DeviceLightDao deviceLightDao;
    @Autowired
    private DeviceAirService deviceAirService;
    @Autowired
    private CustomerAreaService novaCustomerAreaService;

    @Override
    public AreaPoint insert(AreaPoint areapoint) {
        AreaPoint areaPoint = checkInsert(areapoint);
        if (areaPoint != null) {
            return areaPoint;
        } else {
            return areaPointDao.insert(areapoint);
        }
    }

    @Override
    public AreaPoint checkInsert(AreaPoint areaPoint) {
        return getByDetail(areaPoint.getGatewayid(), areaPoint.getSensorid(), areaPoint.getChannelid());
    }

    @Override
    public int delete(AreaPoint areapoint) {
        return areaPointDao.deleteByExample(areapoint);
    }

    @Override
    public int update(AreaPoint areapoint) {
        return areaPointDao.updateNotNull(areapoint);
    }

    @Override
    public PageList<AreaPoint> findAreaPointPageList(AreaPoint areapoint,
                                                     Page page, String sidx, String sord) {
        if (com.nova.module.util.StringUtils.isNotNullOrEmpty(areapoint.getPointname())) {
            areapoint.setPointname(areapoint.getPointname().trim());
        }
        Query<AreaPoint> query = areaPointDao.query().addOrder(Order.custom(sord, sidx));
        if (areapoint.getDevicetypeid() != null && areapoint.getDevicetypeid() != 0) {
            query.add(Restrictions.eq("t_area_point.DeviceTypeId", areapoint.getDevicetypeid()));
        }
        Filter.addFilterCondition(query, areapoint);
        return query.pageList(page);
    }

    @Override
    public AreaPoint findById(int id) {
        return areaPointDao.getById(String.valueOf(id));
    }

    @Override
    public AreaPoint getByDetail(String gatewayid, Integer sensorid, Integer channelid) {
        return areaPointDao.query().add(Restrictions.eq("t_area_point.GatewayID", gatewayid))
                .add(Restrictions.eq("t_area_point.SensorID", sensorid))
                .add(Restrictions.eq("t_area_point.ChannelID", channelid)).get();
    }

    @Override
    public List<AreaPoint> findByExample(AreaPoint areaPoint) {
        return areaPointDao.findByExample(areaPoint);
    }

    @Override
    public void insertByExcel(List<ExcelArea> list) {
        for (int i = 0; i < list.size(); i++) {
            ExcelArea excelArea = list.get(i);
            AreaPoint areaPoint = new AreaPoint();
            areaPoint.setSensorid(excelArea.getSensorid());
            areaPoint.setChannelid(excelArea.getChannelid());
            areaPoint.setAreaid(excelArea.getAreaid());
            areaPoint.setGatewayid(excelArea.getGatewayid());
            areaPoint.setCustomerinfoid(excelArea.getCustomerinfoid());
            if (StringUtils.isNotNullOrEmpty(excelArea.getDevicetypename())) {
                DeviceType deviceType = deviceTypeDao.query()
                        .add(Restrictions.eq("t_device_type.Name", excelArea.getDevicetypename())).get();
                areaPoint.setDevicetypeid(deviceType.getId());
            }
            excelArea.setPointid(insert(areaPoint).getId());
        }
    }

    @Override
    public void updatePower(Integer type, Integer pointid, Integer num, Integer devicetid) {

        AreaPoint areaPoint = findById(pointid);
        int power = 0;
        if (ModelConstant.AIR_AIRTYPE == type) {
            power = deviceAirService.getById(devicetid).getPower();
        } else {
            power = deviceLightDao.getById(devicetid).getPower();
        }
        if (ModelConstant.TRA_LIGHTTYPE == type) {
            int powers = areaPoint.getPower1();
            int powerchange = num * power;
            areaPoint.setPower1(powers + powerchange);
            update(areaPoint);
        } else if (ModelConstant.LED_LIGHTTYPE == type) {
            int powers = areaPoint.getPower2();
            int powerchange = num * power;
            areaPoint.setPower2(powers + powerchange);
            update(areaPoint);
        } else {
            int powers = areaPoint.getRealpower();
            int powerchange = num * power;
            areaPoint.setRealpower(powers + powerchange);
            update(areaPoint);
        }
    }

    @Override
    public List<String> getAllGateway() {
        String sql = "SELECT DISTINCT (GatewayID) FROM t_area_point";
        List<String> result = JdbcUtils.query(String.class, sql);
        return result;
    }

    @Override
    public List<Map<String, Object>> getAllGatewayCustomer() {
        String sql = "SELECT DISTINCT" +
                "  (GatewayID)                 label," +
                "  t_customerinfo.CustomerName customer " +
                "FROM t_area_point " +
                "  JOIN t_customerinfo ON t_customerinfo.ID = t_area_point.CustomerInfoId " +
                "ORDER BY t_customerinfo.CustomerName;";
        List<Map<String, Object>> maps = JdbcUtils.queryMap(sql);
        return maps;
    }

    @Override
    public List<String> getCustomerGateway(Integer id) {
        String sql = "SELECT DISTINCT (GatewayID) FROM t_area_point WHERE CustomerInfoId = ? ";
        List<String> result = JdbcUtils.query(String.class, sql, id);
        return result;
    }

    @Override
    public Integer getCompanyCode(AreaPoint areaPoint) {
        AreaPoint result = areaPointDao.query(areaPoint).get();
        if (result != null && result.getCustomerinfoid() != null) {
            CustomerInfo customerInfo = novaCustomerInfoDao.getById(result.getCustomerinfoid());
            return customerInfo.getCompanycode();
        }
        return 0;
    }

    @Override
    public List<Map<String, Object>> findDeviceDetail(Integer areaId) {
        SqlBuffer sqlBuffer = new SqlBuffer(
                "SELECT " +
                        "  t_device_type.Name DeviceName, " +
                        "  sum(1)             Num " +
                        "FROM t_area_point " +
                        "  LEFT JOIN t_device_type ON t_device_type.ID = t_area_point.DeviceTypeId " +
                        "  WHERE t_area_point.AreaId = ? " +
                        "GROUP BY t_device_type.Name ", true);

        List<Map<String, Object>> result = JdbcUtils.queryMap(
                sqlBuffer.getSelectSql(), areaId);
        return result;
    }

    @Override
    public boolean existAreaPoint(AreaPoint areaPoint) {
        Query<AreaPoint> query = areaPointDao.query()
                .add(Restrictions.eq("GatewayID", areaPoint.getGatewayid()))
                .add(Restrictions.eq("SensorID", areaPoint.getSensorid()))
                .add(Restrictions.eq("ChannelID", areaPoint.getChannelid()));
        if (areaPoint.getId() != null && areaPoint.getId() != 0) {
            query.add(Restrictions.ne("t_area_point.ID", areaPoint.getId()));
        }
        int count = query.list().size();
        return count == 0;
    }

    @Override
    public List<AreaPoint> findPageAreaPointStatus(AreaPoint areaPoint, int companycode, String summaryDate, Page page, String sidx, String sord) {
        SqlBuffer sqlBuffer = new SqlBuffer(SQLParser.sql("areaPointSQL.findPageAreaPointStatus"), false);
        sqlBuffer.appendAnd("t_area_point.CustomerInfoId", areaPoint.getCustomerinfoid());
        sqlBuffer.appendAnd("t_area_point.DeviceTypeId", areaPoint.getDevicetypeid());
        if (StringUtils.isNotNullOrEmpty(areaPoint.getGatewayid())) {
            sqlBuffer.appendAnd("t_area_point.GatewayID", areaPoint.getGatewayid());
        }
        if (areaPoint.getSensorid() != null && areaPoint.getSensorid() != 0) {
            sqlBuffer.appendAnd("t_area_point.SensorID", areaPoint.getSensorid());
        }
        sqlBuffer.appendOrder(sidx, sord);
        int rowCount = JdbcUtils.count(sqlBuffer.getCountSql(),
                companycode, summaryDate).intValue();
        List<AreaPoint> areaPoints = JdbcUtils.query_limit(
                AreaPoint.class, sqlBuffer.getSelectSql(), page,
                companycode, summaryDate);
        page.make(rowCount);
        return areaPoints;
    }

    @Override
    public List<AreaPoint> findAll() {
        return areaPointDao.query()
                .addOrder(Order.asc("GatewayID,SensorID ,ChannelID")).list();

    }

    @Override
    public void findStatu(AreaPoint point) {
        String sql = "SELECT gateway_id,gateway_logo FROM gateway_conf WHERE gateway_logo = ? ";
        List<Map<String, Object>> mapList = JdbcUtils.queryMap(sql, point.getGatewayid());
        for (Map<String, Object> map : mapList) {
            String tableName = "iot_history_" + map.get("gateway_id");
            String selsql = "SELECT data_time  startdate,value activestate " +
                    " FROM  " + tableName +
                    " WHERE gateway_logo = ? AND sensor_name = ? AND channel_name = ? " +
                    " ORDER BY data_time DESC LIMIT 1 ";
            List<Gather> query = JdbcUtils.query(Gather.class, selsql, point.getGatewayid(), point.getSensorid(), point.getChannelid());
            if (query != null && query.size() > 0) {
                point.setStatus(query.get(0).getActivestate());
            }
        }
    }

    @Override
    public void findActivetime(String startday, String endday, List<AreaPoint> areaPointList, Integer state) {
        Timestamp startData = Timestamp.valueOf(startday);
        Timestamp endData = Timestamp.valueOf(endday);
        List<String> gateways = getGateway(areaPointList);
        String sql = "SELECT gateway_id,gateway_logo FROM gateway_conf ";
        List<Map<String, Object>> mapList = JdbcUtils.queryMap(sql);
        for (int i = 0; i < gateways.size(); i++) {
            one:
            for (Map<String, Object> map : mapList) {
                if (((String) map.get("gateway_logo")).trim().equals(gateways.get(i).trim())) {
                    String tableName = "iot_history_" + map.get("gateway_id");
                    String dataSql = "SELECT " +
                            "  gather.gateway_logo gatewayid , " +
                            "  gather.sensor_name sensorid , " +
                            "  gather.channel_name channelid , " +
                            "  sum(value) activetime " +
                            " FROM ( " +
                            "       SELECT " +
                            "         gateway_logo, " +
                            "         sensor_name, " +
                            "         channel_name, " +
                            "         value FROM " + tableName +
                            "       WHERE data_time BETWEEN ? AND ? AND value = ? " +
                            "       GROUP BY gateway_logo, sensor_name, channel_name, to_char(data_time, 'yyyy-mm-dd hh24:mi:00'), value) gather " +
                            "GROUP BY gateway_logo, sensor_name, channel_name, value";
                    List<Gather> gatherLists = JdbcUtils.query(Gather.class, dataSql, startData, endData, state);
                    if (gatherLists.size() > 0) {
                        for (AreaPoint areaPoint : areaPointList) {
                            two:
                            for (Gather gather : gatherLists) {
                                if (areaPoint.getGatewayid().equals(gather.getGatewayid().trim())
                                        && areaPoint.getSensorid() - gather.getSensorid() == 0
                                        && areaPoint.getChannelid() - gather.getChannelid() == 0) {
                                    areaPoint.setUseminute(gather.getActivetime());
                                    break two;
                                }
                            }
                        }
                    }
                    break one;
                }
            }
        }
    }

    @Override
    public List<String> getGateway(List<AreaPoint> areaPoints) {
        ArrayList<String> strings = new ArrayList<String>();
        HashSet<String> set = new HashSet<String>();
        if (areaPoints != null && areaPoints.size() > 0) {
            for (int i = 0; i < areaPoints.size(); i++) {
                set.add(areaPoints.get(i).getGatewayid());
            }
            strings.addAll(set);
        }

        return strings;
    }

    @Override
    public List<AreaPoint> getByCompany(CustomerInfo customerInfo) {
        if (customerInfo != null) {
            return areaPointDao.query().add(Restrictions.eq("CustomerInfoId", customerInfo.getId())).list();
        } else {
            return null;
        }
    }

    @Override
    public List<AreaPoint> getByCompany(List<CustomerInfo> customerInfoList) {
        ArrayList<AreaPoint> areaPointList = new ArrayList<AreaPoint>();
        for (CustomerInfo customerInfo : customerInfoList) {
            List<AreaPoint> areaPoints = getByCompany(customerInfo);
            areaPointList.addAll(areaPoints);
        }
        return areaPointList;
    }

    @Override
    public double getEnergy(List<AreaPoint> areaPoints) {
        double energy = 0.0;
        for (AreaPoint areaPoint : areaPoints) {
            int useminute = areaPoint.getUseminute() == null ? 0 : areaPoint.getUseminute();
            energy = energy + areaPoint.getRealpower() * useminute;
        }
        return energy / 60000;
    }

    @Override
    public AreaPoint getByarea(String areaName, Integer customerinfoId) {
        CustomerArea area = novaCustomerAreaService.getByarea(areaName, customerinfoId);
        if (area == null) {
            return null;
        } else {
            return areaPointDao.query().add(Restrictions.eq("AreaId", area.getId())).get();
        }
    }


    @Override
    public void fortrim(AreaPoint areaPoint) {
        areaPoint.setRelevancename(areaPoint.getRelevancename().trim());
    }

    @Override
    public List<String> getCustomerGateway(List<CustomerInfo> customerInfoList) {
        List<String> strings = new ArrayList<String>();
        for (CustomerInfo customerInfo : customerInfoList) {
            List<String> customerGateway = getCustomerGateway(customerInfo.getId());
            strings.addAll(customerGateway);
        }
        return strings;
    }

    @Override
    public List<Map<String, Object>> findPointOnly(Integer customerinfoId) {
        SqlBuffer sqlBuffer = new SqlBuffer(
                "SELECT " +
                        "  point.ID pointid, " +
                        "  AreaName " +
                        "FROM ( " +
                        "       SELECT " +
                        "         *, " +
                        "         count(1) num " +
                        "       FROM t_area_point " +
                        "       WHERE CustomerInfoId = ? " +
                        "       GROUP BY AreaId) point LEFT JOIN t_customer_area ON point.AreaId = t_customer_area.ID " +
                        "WHERE point.num = 1", true);

        List<Map<String, Object>> result = JdbcUtils.queryMap(
                sqlBuffer.getSelectSql(), customerinfoId);
        return result;
    }

    @Override
    public List<AreaPoint> findAreaMainPoint(Integer customerinfoId, String areaName) {
        String sql = "SELECT " +
                "  t_area_point.* " +
                "FROM t_customerinfo " +
                "  JOIN t_customer_area ON t_customer_area.CustomerInfoId = t_customerinfo.ID " +
                "  JOIN t_area_point ON AreaId = t_customer_area.ID " +
                "WHERE t_customerinfo.ID = ? # " +
                "GROUP BY AreaId";
        if(StringUtils.isNotNullOrEmpty(areaName)){
            sql = sql.replace("#","AND AreaName = '" + areaName + "'");
        }else {
            sql = sql.replace("#","");
        }
        List<AreaPoint> query = JdbcUtils.query(AreaPoint.class, sql, customerinfoId);
        return query;
    }
}
